CREATE TABLE [dbo].[tb_LiquidacionBYP]
(
[idLiquidacionByP] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IdLineaDeByP] [int] NOT NULL,
[NumeroPeriodo] [int] NOT NULL,
[Año] [int] NOT NULL,
[Pagado] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tb_LiquidacionBYP_Pagado] DEFAULT ('N')
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[ControlarLiquidacionPagado] ON [dbo].[tb_LiquidacionBYP]
for  UPDATE 
AS
SET NOCOUNT ON
DECLARE @Descripcion as varchar(100), @IdLineaDeByP as int,@NumeroPeriodo as int,@Año as int,@Pagado as varchar(1)
DECLARE @Descripcion2 as varchar(100), @IdLineaDeByP2 as int,@NumeroPeriodo2 as int,@Año2 as int,@Pagado2 as varchar(1)
declare @msg as varchar

SELECT   @Descripcion =Descripcion,@IdLineaDeByP=IdLineaDeByP,@NumeroPeriodo=NumeroPeriodo,@Año=Año,@Pagado=Pagado  FROM inserted

SELECT    @Descripcion2 =Descripcion,@IdLineaDeByP2=IdLineaDeByP,@NumeroPeriodo2=NumeroPeriodo,@Año2=Año,@Pagado2=Pagado  FROM deleted

if ((@Descripcion<>@Descripcion2) or (@IdLineaDeByP<>@IdLineaDeByP2) or  (@NumeroPeriodo<>@NumeroPeriodo2) or (@Año<>@Año2) ) or  (upper(@Pagado2)='S' )
 BEGIN
		ROLLBACK transaction 
		RAISERROR('Sólo se puede modificar el campo PAGADO de N a S.', 16,1)
 END

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[GenerarMovimientosLiquidacion] ON [dbo].[tb_LiquidacionBYP]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @idLiquidacionByP int , @IdLineaDeByP as int


SELECT @idLiquidacionByP=idLiquidacionByP, @IdLineaDeByP=IdLineaDeByP FROM inserted

IF (SELECT count( *) FROM sagenet.dbo.tb_lineasdeByP WHERE idlineadebyp=@IdLineaDeByP) <1
 BEGIN
	ROLLBACK transaction 
	RAISERROR('No existe la LINEA asociada a esta LIQUIDACION, Debe crear la linea previamente.', 16,1)
 END
ELSE
	BEGIN
		INSERT INTO tb_MovimientosBYP( [IdBecaYPrestamo], [LiquidacionByP], [Monto], [Fecha], [IdTipoDeMovimiento])
		SELECT idbeca,@idLiquidacionByP,montomensual,getdate() ,1
		FROM tb_becasyprestamos WHERE lineadebyp=@IdLineaDeByP
	END

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[GenerearDescripcionLiq] ON [dbo].[tb_LiquidacionBYP]
instead of INSERT
AS
SET NOCOUNT ON
DECLARE @idLiquidacionByP int ,@descripcion varchar(50), @IdLineaDeByP as int,@NumeroPeriodo as int,@Año as int,@Pagado as varchar(1)


SELECT @idLiquidacionByP=idLiquidacionByP, @IdLineaDeByP=IdLineaDeByP,@descripcion=descripcion, @NumeroPeriodo=numeroperiodo,@Año=año,@Pagado=pagado  FROM inserted


--IF (@numeroperiodo) in (0,1,2,3,4,5,6)
 --  BEGIN

SELECT @NUMEROPERIODO=(SELECT Numeroperiodo FROM TB_LINEASDEBYP WHERE IDLINEADEBYP=@IDLINEADEBYP)

                          SELECT @DESCRIPCION=(SELECT DESCRIPCION FROM TB_LINEASDEBYP WHERE IDLINEADEBYP=@IDLINEADEBYP)+ '-0'+ CAST(@NUMEROPERIODO AS VARCHAR(1) )
		INSERT INTO tb_LiquidacionBYP ([Descripcion], [IdLineaDeByP], [NumeroPeriodo], [Año], [Pagado])
	             VALUES( @descripcion,@IdLineaDeByP, @NumeroPeriodo,@Año,@Pagado)
		--update tb_LiquidacionBYP set Descripcion=@Descripcion where idLiquidacionByP=@idLiquidacionByP
--    END
--else
 --  BEGIN
--	ROLLBACK transaction 
--	RAISERROR('El Número de período debe estar comprendido entre 0 y 6 !!! (0 = anual).', 16,1)
 --  END


GO
ALTER TABLE [dbo].[tb_LiquidacionBYP] ADD CONSTRAINT [PK_tb_PeriodosDeLiquidacionBYP] PRIMARY KEY CLUSTERED  ([idLiquidacionByP]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_LiquidacionBYP] WITH NOCHECK ADD CONSTRAINT [FK_tb_LiquidacionBYP_tb_LineasDeBYP] FOREIGN KEY ([IdLineaDeByP]) REFERENCES [dbo].[tb_LineasDeBYP] ([idLineaDeBYP])
GO
